Skip Headers

Oracle® Database Application Developer's Guide - Rules Manager and Expression Filter 10g Release 2 (10.2) Part Number B14288-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 Oracle Expression Filter Concepts

Oracle Expression Filter, a feature of Oracle Database 10g, is a component of Rules Manager that allows application developers to store, index, and evaluate conditional expressions (expressions) in one or more columns of a relational table. Expressions are a useful way to describe interests in expected data.

Expression Filter matches incoming data with expressions stored in a column to identify rows of interest. It can also derive complex relationships by matching data in one table with expressions in a second table. Expression Filter simplifies SQL queries; allows expressions to be inserted, updated, and deleted without changing the application; and enables reuse of conditional expressions in rules by separating them from the application and storing them in the database. Applications involving information distribution, demand analysis, and task assignment can benefit from Expression Filter.

## What Is Expression Filter?

Expression Filter provides a data type, operator, and index type to store, evaluate, and index expressions that describe an interest in a data item or piece of information. See Oracle Database Data Cartridge Developer's Guide for an explanation of these terms. Expressions are stored in a column of a user table. Expression Filter matches expressions in a column with a data item passed by a SQL statement or with data stored in one or more tables, and evaluates each expression to be true or false. Optionally, expressions can be indexed when using the Enterprise Edition of Oracle Database. Expression Filter includes the following elements:

- Attribute set: a definition of the event and its set of attributes

- Expression data type: A virtual data type created through a constraint placed on a `VARCHAR2` column in a user table that stores expressions

- `EVALUATE` operator: An operator that evaluates expressions for each data item

- Administrative utilities: A set of utilities that validate expressions and suggest optimal index structure

- Expression indexing: An index that enhances performance of the `EVALUATE` operator for large expression sets. Expression indexing is available in Oracle Database Enterprise Edition

### Expression Filter Usage Scenarios

This section provides examples of how you can use Expression Filter.

Match Incoming Data with Conditional Expressions

Expression Filter can match incoming data with conditional expressions stored in the database to identify rows of interest. For example, consider an application that matches buyers and sellers of cars. A table called `Consumer` includes a column called `BUYER_PREFERENCES` with an Expression data type. The `BUYER_PREFERENCES` column stores an expression for each consumer that describes the kind of car the consumer wants to purchase, including make, model, year, mileage, color, options, and price. Data about cars for sale is included with the `EVALUATE` operator in the SQL `WHERE` clause. The SQL `EVALUATE` operator matches the incoming car data with the expressions to find prospective buyers.

The SQL `EVALUATE` operator also enables batch processing of incoming data. Data can be stored in a table called `CARS` and matched with expressions stored in the `CONSUMER` table using a join between the two tables.

The SQL `EVALUATE` operator saves time by matching a set of expressions with incoming data and enabling large expression sets to be indexed for performance. This saves labor by allowing expressions to be inserted, updated, and deleted without changing the application and providing a results set that can be manipulated in the same SQL statement, for instance to order or group results. In contrast, a procedural approach stores results in a temporary table that must be queried for further processing, and those expressions cannot be indexed.

Maintain Complex Table Relationships

Expression Filter can convey N-to-M (many-to-many) relationships between tables. Using the previous example:

- A car may be of interest to one or more buyers.

- A buyer may be interested in one or more cars.

- A seller may be interested in one or more buyers.

To answer questions about these relationships, the incoming data about cars is stored in a table called `CARS` with an Expression column (column of Expression data type) called `SELLER_PREFERENCES`. The `CONSUMERS` table includes a column called `BUYER_PREFERENCES`. The SQL `EVALUATE` operator can answer questions such as:

- What cars are of interest to each consumer?

- What buyers are of interest to each seller?

- What demand exists for each car? This can help to determine optimal pricing.

- What unsatisfied demand is there? This can help to determine inventory requirements.

This declarative approach saves labor. No action is needed if changes are made to the data or the expressions. Compare this to the traditional approach where a mapping table is created to store the relationship between the two tables. A trigger must be defined to recompute the relationships and to update the mapping table if the data or expressions change. In this case, new data must be compared to all expressions, and a new expression must be compared to all data.

Application Attributes

Expression Filter is a good fit for applications where the data has the following attributes:

- A large number of data items exist to be evaluated.

- Each data item has structured data attributes, for example `VARCHAR, NUMBER, DATE, XMLTYPE`.

- Incoming data is evaluated by a significant number of unique and persistent queries containing expressions.

- The expression (in the SQL `WHERE` clause) describes an interest in incoming data items.

- The expressions compare attributes to values using relational operators (=, !=, <, >, and so on).

## Introduction to Expressions

Expressions describe interests in an item of data. Expressions are stored in a column of a user table and compared, using the SQL `EVALUATE` operator, to incoming data items specified in a SQL `WHERE` clause or to a table of data. Expressions are evaluated as true or false, or return a null value if an expression does not exist for a row.

An expression describes interest in an item of data using one or more variables, known as elementary attributes. An expression can also include literals, functions supplied by Oracle, user-defined functions, and table aliases. A valid expression consists of one or more simple conditions called predicates. The predicates in the expression are linked by the logical operators `AND` and `OR`. Expressions must adhere to the SQL `WHERE` clause format. (For more information about the SQL `WHERE` clause, see Oracle Database SQL Reference.) An expression is not required to use all the defined elementary attributes; however, the incoming data must provide a value for every elementary attribute. Null is an acceptable value.

For example, the following expression includes the `UPPER` function supplied by Oracle and captures the interest of a user in a car (the data item) with the model, price, and year as elementary attributes:

    UPPER(Model) = 'TAURUS' and Price < 20000 and Year > 2000

Expressions are stored in a column of a user table with an Expression data type. The values stored in a column of this type are constrained to be expressions. (See Chapter10.) A user table can have one or more Expression columns. A query to display the contents of an Expression column displays the expressions in string format.

You insert, update, and delete expressions using standard SQL. A group of expressions that are stored in a single column is called an expression set and shares a common set of elementary attributes. This set of elementary attributes plus any functions used in the expressions are the metadata for the expression set. This metadata is referred to as the attribute set. The attribute set consists of the elementary attribute names and their data types and any functions used in the expressions. The attribute set is used by the Expression column to validate changes and additions to the expression set. An expression stored in the Expression column can use only the elementary attribute and functions defined in the corresponding attribute set. Expressions cannot contain subqueries.

Expression Filter provides the `DBMS_EXPFIL` package which contains procedures to manage the expression data.

There are four basic steps to create and use an Expression column:

1. Define an attribute set. See Chapter10.

2. Define an Expression column in a user table. See Chapter10.

3. Insert expressions in the table. See Chapter10.

4. Apply the SQL `EVALUATE` operator to compare expressions to incoming data items. See Chapter10.

Figure 10-1 shows the process steps for creating and implementing a rules application based on Expression Filter. The remaining sections in this chapter guide you through this procedure.

Figure 10-1 Expression Filter Implementation Process for a Rules Application

Description of "Figure 10-1 Expression Filter Implementation Process for a Rules Application"

### Defining Attribute Sets

A special form of an Oracle object type is used to create an attribute set. (For more information about object types, see Oracle Database Application Developer's Guide - Object-Relational Features.)

The attribute set defines the elementary attributes for an expression set. It implicitly allows all SQL functions supplied by Oracle to be valid references in the expression set. If the expression set refers to a user-defined function, it must be explicitly added to the attribute set. An elementary attribute in an attribute set can refer to data stored in another database table using table alias constructs. One or more or all elementary attributes in an attribute set can be table aliases. If an elementary attribute is a table alias, the value assigned to the elementary attribute is a `ROWID` from the corresponding table. For more information about table aliases, see Appendix A.

You can create an attribute set using one of two approaches:

- Use an existing object type to create an attribute set with the same name as the object type. This approach is most appropriate to use when the attribute set does not contain any table alias elementary attributes. You use the `CREATE_ATTRIBUTE_SET` procedure of the `DBMS_EXPFIL` package. See Example 10-1.

- Individually add elementary attributes to an existing attribute set. Expression Filter automatically creates an object type to encapsulate the elementary attributes and gives it the same name as the attribute set. This approach is most appropriate to use when the attribute set contains one or more elementary attributes defined as table aliases. You use the `ADD_ELEMENTARY_ATTRIBUTE` procedure of the `DBMS_EXPFIL` package. See Example 10-2.

If the expressions refer to user-defined functions, you must add the functions to the corresponding attribute set, using the `ADD_FUNCTIONS` procedure of the `DBMS_EXPFIL` package. See Example 10-3.

Attribute Set Examples

Example 10-1 shows how to use an existing object type to create an attribute set. It uses the `CREATE_ATTRIBUTE_SET` procedure.

Example 10-1 Defining an Attribute Set From an Existing Object Type

    CREATE OR REPLACE TYPE Car4Sale AS OBJECT
                                       (Model   VARCHAR2(20),
                                        Year    NUMBER,
                                        Price   NUMBER,
                                        Mileage NUMBER);
    /

    BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set  => 'Car4Sale',
                                       from_type => 'YES');
    END;
    /

For more information about the `CREATE_ATTRIBUTE_SET` procedure, see `CREATE_ATTRIBUTE_SET` Procedure.

Example 10-2 shows how to create an attribute set `Car4Sale` and how to define the variables one at a time. It uses the `CREATE_ATTRIBUTE_SET` and `ADD_ELEMENTARY_ATTRIBUTE` procedures.

Example 10-2 Defining an Attribute Set Incrementally

    BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'Car4Sale');
      DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                                   attr_set   => 'Car4Sale',
                                   attr_name  => 'Model',
                                   attr_type  => 'VARCHAR2(20)');
      DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                                   attr_set   => 'Car4Sale',
                                   attr_name  => 'Year',
                                   attr_type  => 'NUMBER',
                                   attr_defv1 => '2000');
      DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                                   attr_set   => 'Car4Sale',
                                   attr_name  => 'Price',
                                   attr_type  => 'NUMBER');
      DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                                   attr_set   => 'Car4Sale',
                                   attr_name  => 'Mileage',
                                   attr_type  => 'NUMBER');
    END;/

For more information about the `ADD_ELEMENTARY_ATTRIBUTE` procedure, see `ADD_ELEMENTARY_ATTRIBUTES` Procedure.

If the expressions refer to user-defined functions, you must add the functions to the corresponding attribute set. Example 10-3 shows how to add user-defined functions, using the `ADD_FUNCTIONS` procedure, to an attribute set.

Example 10-3 Adding User-Defined Functions to an Attribute Set

    CREATE or REPLACE FUNCTION HorsePower(Model VARCHAR2, Year VARCHAR2)    return NUMBER isBEGIN
    -- Derive HorsePower from other relational tables uisng Model and Year values.--
      return 200;
    END HorsePower;
    /

    CREATE or REPLACE FUNCTION CrashTestRating(Model VARCHAR2, Year VARCHAR2)
        return NUMBER is
    BEGIN
    -- Derive CrashTestRating from other relational tables using Model --
    -- and Year values. --
      return 5;
    END CrashTestRating;
    /

    BEGIN
      DBMS_EXPFIL.ADD_FUNCTIONS (attr_set   => 'Car4Sale',
                                 funcs_name => 'HorsePower');
      DBMS_EXPFIL.ADD_FUNCTIONS (attr_set   => 'Car4Sale',
                                 funcs_name => 'CrashTestRating');
    END;
    /

For more information about the `ADD_FUNCTIONS` procedure, see `ADD_FUNCTIONS` Procedure.

To drop an attribute set, you use the `DROP_ATTRIBUTE_SET` procedure. For more information, see `DROP_ATTRIBUTE_SET` Procedure.

### Defining Expression Columns

Expression is a virtual data type. Assigning an attribute set to a `VARCHAR2` column in a user table creates an Expression column. The attribute set determines which elementary attributes and user-defined functions can be used in the expression set. An attribute set can be used to create multiple columns of `EXPRESSION` data type in the same table and in other tables in the same schema. Note that an attribute set in one schema cannot be associated with a column in another schema.

To create an Expression column:

1. Add a `VARCHAR2` column to a table or create a table with the `VARCHAR2` column. An existing `VARCHAR2` column in a user table can also be used for this purpose. The following example creates a table with a `VARCHAR2` column, named `Interest`, that will be used with an attribute set:

       CREATE TABLE Consumer (CId          NUMBER,
                              Zipcode      NUMBER,
                              Phone        VARCHAR2(12),
                              Interest     VARCHAR2(200));

2. Assign an attribute set to the column, using the `ASSIGN_ATTRIBUTE_SET` procedure. The following example assigns an attribute set to a column named `Interest` in a table called `Consumer`:

       BEGIN
         DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (
                                           attr_set => 'Car4Sale',
                                           expr_tab => 'Consumer',
                                           expr_col => 'Interest');
       END;
       /

   For more information about the `ASSIGN_ATTRIBUTE_SET` procedure, see `ASSIGN_ATTRIBUTE_SET` Procedure.

Figure 10-2 is a conceptual image of consumers' interests (in trading cars) being captured in a `Consumer` table.

Figure 10-2 Expression Data Type

Description of "Figure 10-2 Expression Data Type"

To remove an attribute set from a column, you use the `UNASSIGN_ATTRIBUTE_SET` procedure of the `DBMS_EXPFIL` package. See `UNASSIGN_ATTRIBUTE_SET` Procedure.

To drop an attribute set not being used for any expression set, you use the `DROP_ATTRIBUTE_SET` procedure of the `DBMS_EXPFIL` package. See `DROP_ATTRIBUTE_SET` Procedure.

To copy an attribute set across schemas, you use the `COPY_ATTRIBUTE_SET` procedure of the `DBMS_EXPFIL` package. See `COPY_ATTRIBUTE_SET` Procedure.

### Inserting, Updating, and Deleting Expressions

You use standard SQL to insert, update, and delete expressions. When an expression is inserted or updated, it is checked for correct syntax and constrained to use the elementary attributes and functions specified in the corresponding attribute set. An error message is returned if the expression is not correct. For more information about evaluation semantics, see Chapter10.

Example 10-4 shows how to insert an expression (the consumer's interest in trading cars, which is depicted in Figure 10-2) into the `Consumer` table using the SQL `INSERT` statement.

Example 10-4 Inserting an Expression into the Consumer Table

    INSERT INTO Consumer VALUES (1, 32611, '917 768 4633',
                'Model=''Taurus'' and Price < 15000 and Mileage < 25000');
    INSERT INTO Consumer VALUES (2, 03060, '603 983 3464',
                    'Model=''Mustang'' and Year > 1999 and Price < 20000');

If an expression refers to a user-defined function, the function must be added to the corresponding attribute set (as shown in Example 10-3). Example 10-5 shows how to insert an expression with a reference to a user-defined function, `HorsePower`, into the `Consumer` table.

Example 10-5 Inserting an Expression That References a User-Defined Function

    INSERT INTO Consumer VALUES (3, 03060, '603 484 7013',
                                'HorsePower(Model, Year) > 200 and Price < 20000');

Expression data can be bulk loaded into an Expression column using SQL*Loader. For more information about bulk loading, see Chapter14.

## Applying the SQL EVALUATE Operator

You use the SQL `EVALUATE` operator in the `WHERE` clause of a SQL statement to compare stored expressions to incoming data items. The SQL `EVALUATE` operator returns `1` for an expression that matches the data item and `0` for an expression that does not match. For any null values stored in the Expression column, the SQL `EVALUATE` operator returns `NULL`.

The SQL `EVALUATE` operator has two arguments: the name of the column storing the expressions and the data item to which the expressions are compared. In the data item argument, values must be provided for all elementary attributes in the attribute set associated with the Expression column. Null is an acceptable value. The data item can be specified either as string-formatted name-value pairs or as an `AnyData` instance.

In the following example, the query returns a row from the `Consumer` table if the expression in the `Interest` column evaluates to true for the data item:

    SELECT * FROM Consumer WHERE
       EVALUATE (Consumer.Interest, <data item>) = 1;

Data Item Formatted as a String

If the values of all the elementary attributes in the attribute set can be represented as readable values, such as those stored in `VARCHAR`, `DATE`, and `NUMBER` data types and the constructors formatted as a string, then the data item can be formatted as a string:

Operator Form

    EVALUATE (VARCHAR2, VARCHAR2)
        returns NUMBER;

Example

    SELECT * FROM Consumer WHERE
        EVALUATE (Consumer.Interest,
                  'Model=>''Mustang'',
                   Year=>2000,
                   Price=>18000,
                   Mileage=>22000'
                   ) = 1;

If a data item does not require a constructor for any of its elementary attribute values, then a list of values provided for the data item can be formatted as a string (name-value pairs) using two `getVarchar` methods (a `STATIC` method and a `MEMBER` method) in the object type associated with the attribute set. The `STATIC` method formats the data item without creating the object instance. The `MEMBER` method can be used if the object instance is already available.

The `STATIC` and `MEMBER` methods are implicitly created for the object type and can be used as shown in the following example:

    SELECT * FROM Consumer WHERE
        EVALUATE (Consumer.Interest,
                  Car4Sale.getVarchar('Mustang',   -- STATIC getVarchar API --
                                       2000,
                                       18000,
                                       22000)
                  ) = 1;

    SELECT * FROM Consumer WHERE
        EVALUATE (Consumer.Interest,
                  Car4Sale('Mustang',
                            2000,
                            18000,
                            22000).getVarchar()     -- MEMBER getVarchar() API --
                  ) = 1;

Data Item Formatted as an AnyData Instance

Any data item can be formatted using an `AnyData` instance. `AnyData` is an object type supplied by Oracle that can hold instances of any Oracle data type, both supplied by Oracle and user-defined. For more information, see Oracle Database Application Developer's Guide - Object-Relational Features.

Operator Form

    EVALUATE (VARCHAR2, AnyData)
      returns NUMBER;

An instance of the object type capturing the corresponding attribute set is converted into an `AnyData` instance using the `AnyData` `convertObject` method. Using the previous example, the data item can be passed to the SQL `EVALUATE` operator by converting the instance of the `Car4Sale` object type into `AnyData`, as shown in the following example:

    SELECT * FROM Consumer WHERE
      EVALUATE (Consumer.Interest,
                AnyData.convertObject(
                 Car4Sale('Mustang',
                             2000,
                             18000,
                             22000))
             ) = 1;

Note: A data item formatted as an `AnyData` instance is converted back into the original object before the expressions are evaluated. To avoid the cost of object type conversions, string-formatted data items are recommended whenever possible.

For the syntax of the SQL `EVALUATE` operator, see "EVALUATE" in Chapter 15. For additional examples of the SQL `EVALUATE` operator, see Appendix B.

## Evaluation Semantics

When an expression is inserted or updated, Expression Filter validates the syntax and ensures that the expression refers to valid elementary attributes and functions associated with the attribute set. The SQL `EVALUATE` operator evaluates expressions using the privileges of the owner of the table that stores the expressions. For instance, if an expression includes a reference to a user-defined function, during its evaluation, the function is executed with the privileges of the owner of the table. References to schema objects with no schema extensions are resolved in the table owner's schema.

An expression that refers to a user-defined function may become invalid if the function is modified or dropped. An invalid expression causes the SQL statement evaluating the expression to fail. To recover from this error, replace the missing or modified function with the original function.

The Expression Validation utility is used to verify an expression set. It identifies expressions that have become invalid since they were inserted, perhaps due to a change made to a user-defined function or table. This utility collects references to the invalid expressions in an exception table. If an exception table is not provided, the utility fails when it encounters the first invalid expression in the expression set.

The following commands collect references to invalid expressions found in the `Consumer` table. The `BUILD_EXCEPTIONS_TABLE` procedure creates the exception table, `InterestExceptions`, in the current schema. The `VALIDATE_EXPRESSIONS` procedure validates the expressions and stores the invalid expressions in the `InterestExceptions` table.

    BEGIN
      DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE (exception_tab => 'InterestExceptions');

      DBMS_EXPFIL.VALIDATE_EXPRESSIONS (expr_tab => 'Consumer',
                                        expr_col => 'Interest',
                                        exception_tab => 'InterestExceptions');
    END;
    /

For more information, see `BUILD_EXCEPTIONS_TABLE` Procedure and `VALIDATE_EXPRESSIONS` Procedure.

## Granting and Revoking Privileges

A user requires `SELECT` privileges on a table storing expressions to evaluate them. The SQL `EVALUATE` operator evaluates expressions using the privileges of the owner of the table that stores the expressions. The privileges of the user issuing the query are not considered.

Expressions can be inserted, updated, and deleted by the owner of the table. Others must have `INSERT` and `UPDATE` privileges for the table, and they must have `INSERT` `EXPRESSION` and `UPDATE` `EXPRESSION` privileges for a specific Expression column in the table to be able to make modifications to it.

In the following example, the owner of the `Consumer` table grants expression privileges, using the `GRANT_PRIVILEGE` procedure, on the `Interest` column to a user named `Andy`:

    BEGIN
      DBMS_EXPFIL.GRANT_PRIVILEGE (expr_tab => 'Consumer',
                                   expr_col => 'Interest',
                                   priv_type => 'INSERT EXPRESSION',
                                   to_user => 'Andy');
    END;
    /

To revoke privileges, use the `REVOKE_PRIVILEGE` procedure.

For more information about granting and revoking privileges, see `GRANT_PRIVILEGE` Procedure and `REVOKE_PRIVILEGE` Procedure.

## Error Messages

The Expression Filter error message numbers are in the range of 38401 to 38600. The error messages are documented in Oracle Database Error Messages.

Oracle error message documentation is only available in HTML. If you only have access to the Oracle Documentation CD, you can browse the error messages by range. Once you find the specific range, use your browser's find in page feature to locate the specific message. When connected to the Internet, you can search for a specific error message using the error message search feature of the Oracle online documentation.